package dto;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import vo.messageVO;
import db.MySql;

public class messageDTO {

	private messageVO mvo;
	private Connection conn = null;
	
	
	//페이지에 관련된 변수
		int listSize = 10;  //페이지당 표시되는 행수
		int blockSize = 5; //한블럭당 표시되는 페이지 수
		int totSize = 0;   //전체 게시물의 갯수
		int totPage = 0;   //전체 페이지 수
		int totBlock = 0;  //전체 블럭 수
		int nowPage = 0;   //현재 페이지
		int nowBlock = 0;  //현재 블럭
		int startNo = 0;   //리스트 목록의 시작 위치
		int endNo = 0;     //리스트 목록의 끝 위치
		int startPage = 0; //현재 블럭에서 표시되는 시작 블럭 번호
		int endPage = 0;   //현재 블럭에서 표시되는 끝 블럭 번호
	
	
	public messageDTO() {
		// TODO Auto-generated constructor stub
	}

	public messageDTO(messageVO mvo) {
		this.mvo = mvo;
		this.nowPage = mvo.getNowPage();
		this.nowBlock = mvo.getNowBlock();
		
		conn = new MySql().getConn();
	}
	
	public void list_size_set(int list_size){
		this.listSize = list_size;
	}
	
	public void page() {	// 페이지 구하기
		String sql = "";
//		System.out.println("페이지 번호 : " + mvo.getChoose_number());
		if(mvo.getChoose_number() == 1){
			sql = "SELECT count(*) cnt FROM semyadmin.message WHERE msg_to = ?";
		}
		else{
			sql = "SELECT count(*) cnt FROM semyadmin.message WHERE msg_from = ?";
		}
		
		try {
//			System.out.println("mvo.getM_id() : " + mvo.getM_id());
//			System.out.println("mvo.getChoose_number() : " + mvo.getChoose_number());
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setString(1, mvo.getM_id());
			ResultSet rs = pst.executeQuery();
			if (rs.next()) {
//				System.out.println("rs.getInt('cnt') : " +  rs.getInt("cnt"));
				totSize = rs.getInt("cnt");

				totPage = (int) Math.ceil(totSize * 1.0 / listSize);
				totBlock = (int) Math.ceil(totPage * 1.0 / blockSize);
				nowBlock = (int) Math.ceil(nowPage * 1.0 / blockSize);

				endPage = nowBlock * blockSize;
				startPage = endPage - blockSize + 1;

				endNo = nowPage * listSize;
				startNo = endNo - listSize + 1;

				if (endPage > totPage)
					endPage = totPage;
				if (endNo > totSize)
					endNo = totSize;
			}
			
//			System.out.println("totSize=" + totSize);
//			
//			System.out.println("totPage=" + totPage);
//			System.out.println("totBlock=" + totBlock);
//			System.out.println("nowBlock=" + nowBlock);
//			
//			System.out.println("totBlock=" + totBlock);
//			System.out.println("nowBlock=" + nowBlock);
//			
//			System.out.println("endPage=" + endPage);
//			System.out.println("startPage=" + startPage);
//			
//			System.out.println("endNo=" + endNo);
//			System.out.println("startNo=" + startNo);
			
			
			rs.close();
			pst.close();

		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public String sqlQuery(int choose_number){
		String sql = "";
		if(choose_number == 1){
			sql =  "	 SELECT *  FROM "
					+ "	 (SELECT msg_info.*, "
					+ " CONCAT(e.e_name, CONCAT(' [', CONCAT(e.e_position,']'))) AS concat_from "
					+ "	 FROM "
					+ "	 (SELECT my_info.name AS name, msg.msg_from AS from_user, "
					+ " msg.msg_to AS to_user , "
					+ "	 date_format(msg.msg_t_date,'%Y년%m월%d일 %p %h:%i') AS to_time, "
					+ "	 date_format(msg.msg_f_date,'%Y년%m월%d일 %p %h:%i') AS from_time, "
					+ " msg.msg_confirm, msg.msg_title, "
					+ " msg.msg_context, msg.msg_number , @RNUM := @RNUM+1 AS RNUM"
					+ "	 FROM (SELECT @RNUM := 0) tmp INNER JOIN "
					+ "	 (SELECT m.m_id AS m_id, "
					+ " CONCAT(e.e_name, CONCAT(' [', CONCAT(e.e_position,']'))) AS name "
					+ " FROM semyadmin.employees e JOIN semyadmin.messenger m "
					+ " ON e.e_number = m.e_number "
					+ "	 WHERE m.m_id = ?) AS my_info  "
					+ " JOIN semyadmin.message msg "
					+ " ON msg.msg_to = my_info.m_id) AS msg_info  "
					+ " JOIN semyadmin.employees e  "
					+ "	 JOIN semyadmin.messenger m "
					+ " ON msg_info.from_user = m.m_id AND m.e_number = e.e_number)m  "
					+ "	 WHERE m.RNUM BETWEEN ? AND ?  "
					+ " ORDER BY m.RNUM ASC  ";
		}
		else{
			sql =  "	 SELECT *  FROM "
					+ "	 (SELECT msg_info.*, "
					+ " CONCAT(e.e_name, CONCAT(' [', CONCAT(e.e_position,']'))) AS concat_from "
					+ "	 FROM "
					+ "	 (SELECT my_info.name AS name, msg.msg_from AS from_user, "
					+ " msg.msg_to AS to_user , "
					+ "	 date_format(msg.msg_t_date,'%Y년%m월%d일 %p %h:%i') AS to_time, "
					+ "	 date_format(msg.msg_f_date,'%Y년%m월%d일 %p %h:%i') AS from_time, "
					+ " msg.msg_confirm, msg.msg_title, "
					+ " msg.msg_context, msg.msg_number , @RNUM := @RNUM+1 AS RNUM"
					+ "	 FROM (SELECT @RNUM := 0) tmp INNER JOIN "
					+ "	 (SELECT m.m_id AS m_id, "
					+ " CONCAT(e.e_name, CONCAT(' [', CONCAT(e.e_position,']'))) AS name "
					+ " FROM semyadmin.employees e JOIN semyadmin.messenger m "
					+ " ON e.e_number = m.e_number "
					+ "	 WHERE m.m_id = ?) AS my_info  "
					+ " JOIN semyadmin.message msg "
					+ " ON msg.msg_from = my_info.m_id) AS msg_info  "
					+ " JOIN semyadmin.employees e  "
					+ "	 JOIN semyadmin.messenger m "
					+ " ON msg_info.to_user = m.m_id AND m.e_number = e.e_number)m  "
					+ "	 WHERE m.RNUM BETWEEN ? AND ?  "
					+ " ORDER BY m.RNUM ASC  ";
		}
		
		return sql;
	}
	
	public ArrayList<messageVO> list() { // list 처리 + 검색
		ArrayList<messageVO> al = new ArrayList<messageVO>();
		
		page();
		
		String sql = sqlQuery(mvo.getChoose_number());
		
		// 1번은 항상 내 아이디
		// 받은 메일함일때 2번은 msg_to 3번은 from_user
		// 보낸 메일함일때 2번은 msg_from 3번은 to_user
		
		try {
//			System.out.println(mvo.getM_id());
//			System.out.println(mvo.getNumber2_text());
//			System.out.println(mvo.getNumber3_text());
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setString(1,  mvo.getM_id() );					// 
			pst.setInt(2, startNo);		// 
			pst.setInt(3, endNo);		// 
//			System.out.println("들어감?111?");
			ResultSet rs = pst.executeQuery();
			while (rs.next()) {
				messageVO v = new messageVO();
				v.setMsg_number(rs.getInt("msg_number"));//
				
				v.setMsg_from(rs.getString("from_user"));
				v.setMsg_f_date(rs.getString("from_time"));//
				
				v.setMsg_to(rs.getString("to_user"));
				v.setMsg_t_date(rs.getString("to_time"));//
				
				v.setMsg_title(rs.getString("msg_title"));
				v.setMsg_confirm(rs.getInt("msg_confirm"));//
				v.setMsg_context(rs.getString("msg_context"));//
				
				v.setT_user_name(rs.getString("name"));// 조건 필요 보낸사람 받은사람
				v.setF_user_name(rs.getString("concat_from"));
				
//				System.out.println("확인 유무 : " + v.getMsg_confirm());
//				System.out.println("메시지 내용 : " + v.getMsg_context());
//				System.out.println("메시지 제목 : " + v.getMsg_title());
//				System.out.println("보낸 날자 : " + v.getMsg_f_date());
//				System.out.println("보낸이 : " + v.getMsg_from());
//				System.out.println("쪽지 번호 : " + v.getMsg_number());
//				System.out.println("받은 날자 : " + v.getMsg_t_date());
//				System.out.println("받은이 아이디 : " + v.getMsg_to());
//				
//				System.out.println("받은사람 : " + v.getT_user_name());
//				System.out.println("보낸사람 : " + v.getF_user_name());
				al.add(v);
			}
			rs.close();
			pst.close();
			conn.close();
		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return al;
	}
	
	
	public messageVO view() { 	// view : 선택한 글 보기 
		messageVO v = new messageVO();
		String sql = "	SELECT msg_from, msg_to,  "
				+ "			date_format(msg_t_date,'%Y년%m월%d일 %p %h:%i') AS to_time,  "
				+ "			date_format(msg_f_date,'%Y년%m월%d일 %p %h:%i') AS from_time, "
				+ "			msg_confirm, msg_context, msg_number, msg_title "
				+ "			FROM semyadmin.message "
				+ "			WHERE msg_number = ? ";

		try {
			PreparedStatement pst = conn.prepareStatement(sql);
//			System.out.println("\n" + "view의 b_number : " + mvo.getMsg_number());
			pst.setInt(1, mvo.getMsg_number());
			ResultSet rs = pst.executeQuery();

			while (rs.next()) {
				
				v.setMsg_number(rs.getInt("msg_number"));//
				
				v.setMsg_from(rs.getString("msg_from"));
				v.setMsg_f_date(rs.getString("from_time"));//
				
				v.setMsg_to(rs.getString("msg_to"));
				v.setMsg_t_date(rs.getString("to_time"));//
				
				v.setMsg_title(rs.getString("msg_title"));
				v.setMsg_confirm(rs.getInt("msg_confirm"));//
				v.setMsg_context(rs.getString("msg_context"));//
				
				v.setT_user_name(mvo.getT_user_name());// 조건 필요 보낸사람 받은사람
				v.setF_user_name(mvo.getF_user_name());
				
//				System.out.println("메시지 뷰 들어옴");
//				System.out.println("확인 유무 : " + v.getMsg_confirm());
//				System.out.println("메시지 내용 : " + v.getMsg_context());
//				System.out.println("메시지 제목 : " + v.getMsg_title());
//				System.out.println("보낸 날자 : " + v.getMsg_f_date());
//				System.out.println("보낸이 : " + v.getMsg_from());
//				System.out.println("쪽지 번호 : " + v.getMsg_number());
//				System.out.println("받은 날자 : " + v.getMsg_t_date());
//				System.out.println("받은이 아이디 : " + v.getMsg_to());
//				
//				System.out.println("받은사람 : " + v.getT_user_name());
//				System.out.println("보낸사람 : " + v.getF_user_name());
//				System.out.println("메시지 타입 : + " + mvo.getChoose_number());
				if(mvo.getChoose_number() == 1)
				checkConfirm(rs.getInt("msg_number"));
			}
			
			rs.close();
			pst.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return v;
	}
	
	public void checkConfirm(int msg_number){
		
		String sql = " UPDATE semyadmin.message "
				+ " SET msg_confirm = 1  "
				+ " WHERE message.msg_number =  ?";
		
		try {
			PreparedStatement pst = conn.prepareStatement(sql);
			pst.setInt(1, msg_number);
			int cnt = pst.executeUpdate();
			if (cnt > 0){
//				System.out.println("정상수정 완료!");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public messageVO getMvo() {
		return mvo;
	}

	public void setMvo(messageVO mvo) {
		this.mvo = mvo;
	}

	public Connection getConn() {
		return conn;
	}

	public void setConn(Connection conn) {
		this.conn = conn;
	}

	public int getListSize() {
		return listSize;
	}

	public void setListSize(int listSize) {
		this.listSize = listSize;
	}

	public int getBlockSize() {
		return blockSize;
	}

	public void setBlockSize(int blockSize) {
		this.blockSize = blockSize;
	}

	public int getTotSize() {
		return totSize;
	}

	public void setTotSize(int totSize) {
		this.totSize = totSize;
	}

	public int getTotPage() {
		return totPage;
	}

	public void setTotPage(int totPage) {
		this.totPage = totPage;
	}

	public int getTotBlock() {
		return totBlock;
	}

	public void setTotBlock(int totBlock) {
		this.totBlock = totBlock;
	}

	public int getNowPage() {
		return nowPage;
	}

	public void setNowPage(int nowPage) {
		this.nowPage = nowPage;
	}

	public int getNowBlock() {
		return nowBlock;
	}

	public void setNowBlock(int nowBlock) {
		this.nowBlock = nowBlock;
	}

	public int getStartNo() {
		return startNo;
	}

	public void setStartNo(int startNo) {
		this.startNo = startNo;
	}

	public int getEndNo() {
		return endNo;
	}

	public void setEndNo(int endNo) {
		this.endNo = endNo;
	}

	public int getStartPage() {
		return startPage;
	}

	public void setStartPage(int startPage) {
		this.startPage = startPage;
	}

	public int getEndPage() {
		return endPage;
	}

	public void setEndPage(int endPage) {
		this.endPage = endPage;
	}

}
